﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Model;

namespace DAL
{
    public  class GuidNoDAL
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetGuidByActivity(string activityId)
        {
            if (string.IsNullOrEmpty(activityId)) return null;

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT ACTIVITY_ID,GUID_STR, CASE GUID_STATUS
            WHEN '0' THEN '未使用'
            WHEN '1' THEN '测试中'
            ELSE '测试完成' END AS GUID_STATUS,
            ISNULL(CONVERT(VARCHAR(100), CREATE_TIME, 20),'') AS CREATE_TIME,
            ISNULL(TESTER_NAME,'') AS TESTER_NAME,
            ISNULL(TESTER_EMAIL,'') AS TESTER_EMAIL,
            ISNULL(CONVERT(VARCHAR(100), TESTER_TESTSTARTTIME, 20),'') AS TESTER_TESTSTARTTIME
            FROM EXAMDB.DBO.TB_GUID_NUMBER LEFT JOIN EXAMDB.DBO.TB_TESTER_INFO 
            ON TB_GUID_NUMBER.GUID_STR=TB_TESTER_INFO.TESTER_SN");

            strSql.Append(" where activity_id = " + activityId );
            strSql.Append(" order by guid_status");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="activity_id"></param>
        public static void Delete(int guidStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete ExamDB.dbo.tb_GUID_Number");
            strSql.Append(" where guid_str=@guidStr");
            SqlParameter[] parameters = {
                                            new SqlParameter("@guidStr",SqlDbType.Int,4)
                                        };
            parameters[0].Value = guidStr;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(GuidNo  model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ExamDB.dbo.tb_GUID_Number(");
            strSql.Append(@"guid_str,
                            activity_id,
                            create_time,
                            guid_status)");

            strSql.Append(" values (");

            strSql.Append(@"@guid_str,
                            @activity_id,
                            @create_time,
                            @guid_status)");

            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@guid_str", SqlDbType.NVarChar,255),
					new SqlParameter("@activity_id", SqlDbType.Int,4),
                    new SqlParameter("@create_time",SqlDbType.DateTime),
                    new SqlParameter("@guid_status",SqlDbType.Int,4)
                                        };
            parameters[0].Value = model.GuidStr;
            parameters[1].Value = model.ActivityId;
            parameters[2].Value = model.CreateTime;
            parameters[3].Value = 0;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static void Update(GuidNo  model)
        {
           //todo add update
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns>-1：无效</returns>
        /// <returns>0：有效-未使用</returns>
        /// <returns>1：有效-测试中</returns>
        /// <returns>2：有效-测试完成</returns>
        public static int GetStatus(string guid)
        {
            int ret = -1;

            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select guid_status from dbo.tb_GUID_Number where guid_str = ");
            strSql.Append("'");
            strSql.Append(guid);
            strSql.Append("'");
            try
            {
                DataSet GUIDds = DbHelperSQL.Query(strSql.ToString());
                if (GUIDds != null && GUIDds.Tables[0].Rows.Count == 1)
                {
                    ret = Int32.Parse(GUIDds.Tables[0].Rows[0][0].ToString());
                    if (ret > 2 || ret < -1)
                    {
                        ret = -1;
                    }
                }
                return ret;
            }
            catch
            {
                ret = -1;
                return ret;
            }
        }

        public static int GetActivityId(string guid)
        {
            int ret = -1;
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"select activity_id from dbo.tb_GUID_Number where guid_str = ");
            strSql.Append("'");
            strSql.Append(guid);
            strSql.Append("'");
            try
            {
                DataSet GUIDds = DbHelperSQL.Query(strSql.ToString());
                if (GUIDds != null && GUIDds.Tables[0].Rows.Count == 1)
                {
                    ret = Int32.Parse(GUIDds.Tables[0].Rows[0][0].ToString());
                }
                return ret;
            }
            catch
            {
                ret = -1;
                return ret;
            }
        }

        public static int UpdateStatus(string guid, int guid_status)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ExamDB.dbo.tb_GUID_Number set guid_status ");
            strSql.Append("='" + guid_status);
            strSql.Append("' where guid_str='");
            strSql.Append(guid + "'");
            if (DbHelperSQL.Query(strSql.ToString()) != null)
            {
                return 1;
            }
            else
            {
                return -1;
            }
        }

    }
}
